Dynamic Views with Structured SQL

Your reports and visualizations have gone over so well, the sales manager wants more! How about the date with the second-highest sales? Oh, and present it for all countries or better yet, let me dynamically specify the country as I'm viewing your visualization! Not to worry, bipp has you covered!

In the SQL, use three levels of nesting of SELECT statements. The country filter is applied at the innermost level. The daily_sales table is marked as a reftable rather than table in the retail dataset. This prevents the table from appearing in the data viewer’s list of tables.

Select the columns:

Set the filters using the Sale Rank from Daily Ranked Sales and Country from Transactions:

bipp generates the SQL:

SELECT t000.invoice_num AS _0, t000.invoice_date AS _1,
    t000.sale_amount AS _2
FROM (
    SELECT t000.invoice_num AS invoice_num,
    t000.invoice_date AS invoice_date,
    sum(t000.amount) AS sale_amount,
    RANK() OVER(ORDER BY (sum(t000.amount)) desc
        ) AS sale_rank
    FROM (
        SELECT t000.invoice_num AS invoice_num,
            cast(t000.invoice_date as DATE) AS invoice_date,
            sum(t000.invoice_qnty * t000.unit_price)
                AS amount
        FROM `online_retail`.`transactions` AS t000
        WHERE (t000.country = 'Germany')
        GROUP BY 1, 2
    ) AS t000
    GROUP BY 1, 2
) AS t000
WHERE (t000.sale_rank = 2)
GROUP BY 1, 2, 3

Fetch the data: